package com.kms.servlet;

import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.kms.console.JSchConsole;
import com.kms.derby.Connections;
import com.kms.model.BondTables;
import com.kms.model.KmsTables;
import com.kms.model.MGroup;
import com.kms.model.MSyspara;
import com.kms.model.SystemParameterBean;
import com.kms.service.SystemparameterService;

public class DatabaseBackupTablesServlet extends HttpServlet{
	SystemparameterService sysservice ;
	
	public void doGet(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		sysservice = (SystemparameterService)Connections.getServiceBean(request, "sysparaService");
		JSchConsole jc = new JSchConsole();
		SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMddHHmmss");
		String datetime = sdf.format(new Date());
		String host = "";
		String user = "";
		String pwd = "";
		String dir = "";
		String dirctory = "";
		String commandstr ="";
		int port = 0;
		
		
		String id = request.getParameter("id");
		MSyspara mssysapp=new MSyspara();
		MSyspara mssysdb=new MSyspara();
		MGroup mgroup = new MGroup();
		if(id !=null && !"null".equals(id)){
			try {
				mgroup = sysservice.selectmGroup(id);
				for(MSyspara sys:mgroup.getListsyspara()){
					if("APP".equals(sys.getDatatype())){
						mssysapp = sys;
					}else if("DB".equals(sys.getDatatype())){
						mssysdb = sys;
					}
				}
			} catch (NumberFormatException e) {
			}
			
			//DB user
			if(mssysdb!=null){
				host = mssysdb.getOsip();
				user = mssysdb.getOsuser();
				pwd = mssysdb.getOspwd();
				dir = mssysdb.getDbzipdir();
				try {
					port = Integer.valueOf(mssysdb.getOsport());
				} catch (NumberFormatException e) {
				}
			}
		}
		
		List<KmsTables> kmstables = getListKmstables(request);
		List<BondTables> bondtables = getListBondtables(request);
		Map<String,SystemParameterBean> map = getListSysSetting(request);
		
		String status = request.getParameter("status1");
		StringBuffer _kmstables = new StringBuffer();
		StringBuffer _bondtables = new StringBuffer();
		String[] kmsschema = request.getParameterValues("kmsschema");
		String[] kmstable = request.getParameterValues("kmstable");
		String[] bondschema = request.getParameterValues("bondschema");
		String[] bondtable = request.getParameterValues("bondtable");
		
		if(kmsschema!=null)
		for(int i=0;i<kmsschema.length;i++){
			_kmstables.append(kmsschema[i]).append(",");
		}
		if(kmstable!=null)
		for(int i=0;i<kmstable.length;i++){
			_kmstables.append(kmstable[i]).append(",");
		}
		if(bondschema!=null)
		for(int i=0;i<bondschema.length;i++){
			_bondtables.append(bondschema[i]).append(",");
		}
		if(bondtable!=null)
		for(int i=0;i<bondtable.length;i++){
			_bondtables.append(bondtable[i]).append(",");
		}
		
		StringBuffer command = new StringBuffer(); 
		String exp_name = "";
		String ret="";
		String tables_ = "";
		try{
			if("backup_table".equals(status)){
				List<String[]> infocommandlist = new ArrayList<String[]>();
				if(_kmstables!=null ){
					infocommandlist.add(new String[]{"kms"," cat .bash_profile",_kmstables.toString()});
				}
				if(_bondtables!=null ){
					infocommandlist.add(new String[]{"bond"," cat .bash_profile",_bondtables.toString()});
				}
				
				for(String[] infotype:infocommandlist){
					ret = ret + jc.sshExecute(host, user, pwd, infotype[1], port);
					String[] l = ret.split("\n");
					StringBuffer _s=new StringBuffer();
					for(String r:l){
						if(r.contains("export") || r.contains("DISPLAY") || r.contains("JAVA_HOME") || r.contains("CLASSPATH") || r.contains("PATH") ){
							_s.append(r);
							_s.append("\n");
						}
					}
					
					_s.append("expdp ");
					if("kms".equals(infotype[0])){
						_s.append(mssysdb.getKmsuser()+"/"+mssysdb.getKmspwd());
						_s.append("@"+mssysdb.getKmsinstance());
						_s.append(" directory="+mssysdb.getKmsdirectory());
					}else if("bond".equals(infotype[0])){
						_s.append(mssysdb.getBonduser()+"/"+mssysdb.getBondpwd());
						_s.append("@"+mssysdb.getBondinstance());
						_s.append(" directory="+mssysdb.getBonddirectory());
					}
					_s.append(" dumpfile=tables"+infotype[0]+datetime+".dmp");
					_s.append(" logfile=tables"+infotype[0]+datetime+".log");
					_s.append(" TABLES="+infotype[2]+" ;");
					ret =ret+ jc.sshExecute(host, user, pwd, _s.toString(), port);
					
					if("kms".equals(infotype[0])){
						dirctory = mssysdb.getKmsdirectorypath();
						commandstr ="cd "+dirctory+" ; zip _expdp_tables_"+infotype[0]+datetime+".zip tables"+infotype[0]+datetime+".* ;";
						ret =ret+ jc.sshExecute(host, user, pwd, commandstr, port);
					}else if("bond".equals(infotype[0])){
						dirctory = mssysdb.getBonddirectorypath();
						commandstr ="cd "+dirctory+" ; zip _expdp_tables_"+infotype[0]+datetime+".zip tables"+infotype[0]+datetime+".* ;";
						ret =ret+ jc.sshExecute(host, user, pwd, commandstr, port);
					}
					//scp /home/kms/kms_server_update.log root@192.168.8.10:/home/kms/monitor
					if("kms".equals(infotype[0])){
						commandstr = "scp "+dirctory+"/_expdp_tables_kms"+datetime+".zip root@"+mssysapp.getTbsmonitorosip()+":"+mssysdb.getDbzipdir();
						ret =ret+ jc.sshExecute(host, user, pwd, commandstr, port);
					}else if("bond".equals(infotype[0])){
						commandstr = "scp "+dirctory+"/_expdp_tables_bond"+datetime+".zip root@"+mssysapp.getTbsmonitorosip()+":"+mssysdb.getDbzipdir();
						ret =ret+ jc.sshExecute(host, user, pwd, commandstr, port);
					}
					
					commandstr = "cd "+dirctory+";rm *"+datetime+".*";
					try{
						port = Integer.valueOf(mssysdb.getOsport());
					}catch(Exception ex){}
					ret =ret+ jc.sshExecute(mssysdb.getOsip(), mssysdb.getOsuser(), mssysdb.getOspwd(), commandstr, port);
				}
			}else if("remove".equals(status)){
				command = new StringBuffer();
				command.append("cd "+mssysdb.getDbzipdir()+"; rm "+mssysdb.getDbzipdir()+"/_*.* ;");
				command.append("ll;");
				try{
					port = Integer.valueOf(mssysapp.getOsport());
				}catch(Exception ex){}
				ret =ret+ jc.sshExecute(mssysapp.getOsip(), mssysapp.getOsuser(), mssysapp.getOspwd(), command.toString(), port);
			}
		}catch(Exception ex){}
		
		request.setAttribute("kmstables", kmstables);
		request.setAttribute("bondtables", bondtables);
		request.setAttribute("mapv", map);
		request.setAttribute("command", ret);
		request.setAttribute("dir", dir);
		request.setAttribute("id", id);
		request.setAttribute("listmgroup",  sysservice.selectAllMGroup());
		request.setAttribute("mssysapp", mssysapp);
		request.setAttribute("mssysdb", mssysdb);
		request.getRequestDispatcher("datebasebackuptables.jsp").forward(request,response); 
		return;
	}

	public void doPost(HttpServletRequest request, HttpServletResponse response)
			throws ServletException, IOException {
		sysservice = (SystemparameterService)Connections.getServiceBean(request, "sysparaService");
		doGet(request, response);
	}
	
	private String exclinuxcommand(String command,Map<String,SystemParameterBean> map,HttpServletRequest request) {
		JSchConsole jc = new JSchConsole();
		String host = "127.0.0.1";
		String user = "kms";
		String pwd = "kms";
		int port = 10022;
		host = map.get("Linux ip")==null?"":map.get("Linux ip").getValue();
		port = Integer.valueOf(map.get("Linuxport")==null?"0":map.get("Linuxport").getValue());
		user = map.get("UserName")==null?"":map.get("UserName").getValue();
		pwd =map.get("Password")==null?"":map.get("Password").getValue();
		String ret="";
		if(command==null){
			command = "ll;";
		}
		try {
			ret = jc.sshExecute(host, user, pwd, command.toString(),port);
		} catch (Exception e) {
			ret = e.toString();
			e.printStackTrace();
		}
		
		return ret;
	}
	private Map<String,SystemParameterBean> getListSysSetting(HttpServletRequest request){
		List<SystemParameterBean> listsysbean = new ArrayList<SystemParameterBean>();
		try {
			Connection conn = Connections.getConnection(request);
			String SQL = "SELECT * FROM SYSSETTING";
			PreparedStatement stmt = conn.prepareStatement(SQL);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				SystemParameterBean sysb = new SystemParameterBean();
				sysb.setId(rs.getInt("Id"));
				sysb.setName(rs.getString("Name"));
				sysb.setType(rs.getString("Type"));
				sysb.setValue(rs.getString("Value"));
				sysb.setEnabled(rs.getString("Enabled"));
				sysb.setDescription(rs.getString("Description"));
				listsysbean.add(sysb);
			}
			
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		Map<String,SystemParameterBean> map = new HashMap<String,SystemParameterBean>();
		for(SystemParameterBean sysb:listsysbean){
			map.put(sysb.getName(), sysb);
		}
		return map;
	}

	private List<KmsTables> getListKmstables(HttpServletRequest request){
		List<KmsTables> listsysbean = new ArrayList<KmsTables>();
		try {
			Connection conn = Connections.getConnection(request);
			String SQL = "SELECT * FROM KMS_TABLES";
			PreparedStatement stmt = conn.prepareStatement(SQL);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				KmsTables sysb = new KmsTables();
				sysb.setId(rs.getInt("ID"));
				sysb.setName(rs.getString("NAME"));
				sysb.setCname(rs.getString("CNAME"));
				sysb.setDescription(rs.getString("DESCRIPTION"));
				listsysbean.add(sysb);
			}
			
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listsysbean;
	}
	
	private List<BondTables> getListBondtables(HttpServletRequest request){
		List<BondTables> listsysbean = new ArrayList<BondTables>();
		try {
			Connection conn = Connections.getConnection(request);
			String SQL = "SELECT * FROM BOND_TABLES";
			PreparedStatement stmt = conn.prepareStatement(SQL);
			ResultSet rs = stmt.executeQuery();
			while (rs.next()) {
				BondTables sysb = new BondTables();
				sysb.setId(rs.getInt("Id"));
				sysb.setName(rs.getString("Name"));
				sysb.setCname(rs.getString("Cname"));
				sysb.setDescription(rs.getString("Description"));
				listsysbean.add(sysb);
			}
			
			rs.close();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return listsysbean;
	}
}
